import xlrd
import pymysql



def main():
	db = connect()
	if db is None:
		print("连接数据库失败")
		return
	
	cursor = db.cursor()

	sql = '''INSERT INTO user_info (id, idcard, username, realname, pwd, telphone, email, age, sex, address, hiredate, sal, job, company)
			VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'''
	
	data = get_data()

	try: 
		cursor.executemany(sql, data)
	except pymysql.MySQLError as e:
		print("插入失败:", e)
		db.rollback()
	else:
		print("插入成功")
		db.commit()
	
	cursor.close()
	db.close()

def connect():
	try: 
		conn = pymysql.connect(
			host='localhost',
			user='root',
			password='123456',
			database='try_db'
		)
	except pymysql.MySQLError as e:
		print("连接数据库失败:", e)
		return None
	return conn


def get_data():
	path = './src/data.xls'
	book = xlrd.open_workbook(path)
	sheet = book.sheet_by_index(0)

	index = 0
	data = []
	for i in range(sheet.nrows):
		if index == 0:
			index += 1
			continue
		row = []
		for j in range(sheet.ncols):
			row.append(sheet.cell_value(i, j))
		data.append(row)

	return data

main()